package com.googlecode.bip.dao.utils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import javax.naming.NamingException;
import org.apache.log4j.Logger;
import cx.ath.choisnet.sql.ConnectionQuery;

/**
 *
 * @author Claude CHOISNET
 */
public final class DBHelper
{
    private final static transient Logger logger = Logger.getLogger( DBHelper.class );

    private DBHelper()
    {
    }

    /**
     * Build a {@link Timestamp} from a string
     *
     * @param formatter DateFormat for parsing string date.
     * @param date      string date
     * @return a Timestamp
     * @throws ParseException if 'date' could not be decoded according to 'format'
     * @throws NullPointerException if one parameter is null
     * @see SimpleDateFormat
     */
    public static Timestamp toTimestamp(
            final DateFormat    formatter,
            final String        date
            )
        throws ParseException, NullPointerException
    {
        if( date == null ) {
            throw new NullPointerException();
            }

        java.util.Date jdate = formatter.parse( date );

        return new Timestamp( jdate.getTime() );
    }

    /**
     * Build a {@link Timestamp} from a string
     *
     * @param format    format for parsing string date.
     * @param date      string date
     * @return a Timestamp
     * @throws ParseException if 'date' could not be decoded according to 'format'
     * @throws NullPointerException if one parameter is null
     * @see SimpleDateFormat
     */
    public static Timestamp toTimestamp(
            final String format,
            final String date
            )
        throws ParseException, NullPointerException
    {
        return toTimestamp( new SimpleDateFormat( format ), date);
    }

    /**
     * Build a {@link Timestamp} from a string
     *
     * @param date string date, format should be "yyyy-MM-dd hh:mm:ss.S"
     * @return a Timestamp
     * @throws ParseException if 'date' could not be decoded according to 'format'
     * @see #toTimestamp(String, String)
     * @throws NullPointerException if 'date' parameter is null
     */
    public static Timestamp toTimestamp(
            final String date
            )
        throws ParseException, NullPointerException
    {
        return toTimestamp( new SimpleDateFormat( "yyyy-MM-dd hh:mm:ss.S" ), date );
    }

    /**
     * @param sqlQuery
     * @param keyFieldName
     * @param valueFieldName
     * @param dataSource
     * @return
     * @throws NamingException
     * @throws SQLException
     */
    public static Map<String,String> getStringStringMap(
            final Connection    connection,
            final String        sqlQuery,
            final String        keyFieldName,
            final String        valueFieldName
            ) throws SQLException
    {
        final ConnectionQuery query = new ConnectionQuery( connection );

        if( logger.isDebugEnabled() ) {
            logger.debug( "SQL: " + sqlQuery );
            }

        try {
            ResultSet          rSet = query.executeQuery( sqlQuery );
            Map<String,String> map  = new LinkedHashMap<String,String>(); // keep order

            while( rSet.next() ) {
                map.put(
                    rSet.getString( keyFieldName ),
                    rSet.getString( valueFieldName )
                    );
                }

            return map;
            }
//        catch( SQLException e ) {
//            slogger.error( "*** getStringStringMap: " + sqlQuery, e );
//
//            return null;
//            }
        finally {
            try {
                query.close();
                }
            catch( Exception e ) {
                logger.warn( "close fail", e );
                }
            }
    }

    public static List<Integer> getListOfInteger(
            final String        columnLabel,
            final Connection    connection,
            final String        sqlQuery
            )
        throws SQLException
    {
        return getList(
                new Listable<Integer>()
                {
                    @Override
                    public List<Integer> getList( String sql )
                            throws SQLException, NamingException
                    {
                        return null;
                    }
                    @Override
                    public Integer newInstance( ResultSet resultSet )
                            throws SQLException
                    {
                        return new Integer( resultSet.getInt( columnLabel ));
                    }
                },
                connection,
                sqlQuery
                );
    }

    /**
     * Return a List of T build for SQL query
     *
     * @param <T>
     * @param listable      {@link Listable} object
     * @param connection    Connection to use for SQL request
     * @param sqlQuery      SQL Query to perform
     * @return a List of T
     * @throws SQLException
     */
    public static <T> List<T> getList(
            final Listable<T>   listable,
            final Connection    connection,
            final String        sqlQuery
            )
        throws SQLException
    {
        final ConnectionQuery query = new ConnectionQuery( connection );

        try {
            if( logger.isDebugEnabled() ) {
                logger.info( "*** getList [" + sqlQuery + "]" );
                }

            try {
                ResultSet   rSet    = query.executeQuery( sqlQuery );
                List<T>     list    = new ArrayList<T>();

                while( rSet.next() ) {
                    list.add( listable.newInstance( rSet ) );
                    }

                rSet.close();

                return list;
            }
            catch( SQLException e ) {
                logger.error(
                        "*** getList [SQLException] for [" + sqlQuery + "]",
                        e
                        );
                throw e;
                }
            }
        finally {
            query.quietClose();
            }
    }

    /**
     * Return
     *
     * @param listable
     * @param sqlQuery
     * @return
     * @throws SQLException
     * @throws NamingException
     */
    public static <T> T get(
            final Listable<T>   listable,
            final String        sqlQuery
            )
        throws SQLException, NamingException
    {
        List<T> l = listable.getList( sqlQuery );

        if( l.size() != 1 ) {
            if( l.size() > 1 ) { // Inconsistent database
                logger.error(
                    "*** getFromList [Inconsistent database]: return more than one entry ("
                        + l.size() +") for [" + sqlQuery + "]"
                    );
                }
            else {
                if( logger.isDebugEnabled() ) {
                    logger.debug( "*** getFromList no value for [" + sqlQuery + "]" );
                    }
                }

            return null;
            }

        return l.get( 0 );
    }

    //
    // Deprecated ------
    //

//    @Deprecated
//    public static Map<String,String> getStringStringMap(
//            final DataSource    dataSource,
//            final String        sqlQuery,
//            final String        keyFieldName,
//            final String        valueFieldName
//            )
//    {
//        final SimpleQuery query = new SimpleQuery( dataSource );
//
//        if( logger.isDebugEnabled() ) {
//            logger.debug( "SQL: " + sqlQuery );
//            }
//
//        try {
//            ResultSet          rSet = query.executeQuery( sqlQuery );
//            Map<String,String> map  = new LinkedHashMap<String,String>(); // keep order
//
//            while( rSet.next() ) {
//                map.put(
//                    rSet.getString( keyFieldName ),
//                    rSet.getString( valueFieldName )
//                    );
//                }
//
//            return map;
//            }
//        catch( SQLException e ) {
//            logger.error( "*** getStringStringMap: " + sqlQuery, e );
//
//            return null;
//            }
//        finally {
//            query.quietClose();
//            }
//    }

//    @Deprecated
//    public static <T> List<T> getList(
//            final Listable<T>   listable,
//            final DataSource    dataSource,
//            final String        sqlQuery
//            ) throws SQLException
//    {
//        final SimpleQuery sq = new SimpleQuery( dataSource );
//
//        try {
//            slogger.info( "*** getList [" + sqlQuery + "]" );
//
//            try {
//                ResultSet   rSet    = sq.executeQuery( sqlQuery );
//                List<T>     list    = new ArrayList<T>();
//
//                while( rSet.next() ) {
//                    //list.add( new Category().set( rSet ) );
//                    list.add( listable.newInstance( rSet ) );
//                    }
//
//                rSet.close();
//
//                return list;
//            }
//            catch( SQLException e ) {
//                slogger.error(
//                        "*** getCategory [SQLException] for [" + sqlQuery + "]",
//                        e
//                        );
//                throw e;
//                }
//            }
//        finally {
//            SimpleQuery.quietClose( sq );
//            }
//    }
}
